package com.husky.easyexcle.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.xmlbeans.impl.util.Base64;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * 	Excel工具类
 * @author wzh
 *
 */
public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    static DecimalFormat ds = new DecimalFormat("0");

    static Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");

    static boolean isENum(String input) {
        //判断输入字符串是否为科学计数法
        return pattern.matcher(input).matches();
    }

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param contentList 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String titleName, String[]title, List<List<String>> contentList, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 表头样式设置
        CellStyle headStyle = headStyle(wb, sheet);
        CellStyle cellStyle = cellStyle(wb, sheet);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        //创建第一行表头
        row.setHeight((short)((short)30*20));
        HSSFCell cellTitle = (HSSFCell) row.createCell(0);
        CellStyle headStyleTitle = headStyleTitle(wb, sheet);
        //设置标题单元格样式
        cellTitle.setCellStyle(headStyleTitle);
        cellTitle.setCellValue(titleName);//设置单元格标题
        CellRangeAddress region = new CellRangeAddress(0,0,0,title.length-1);
        sheet.addMergedRegion(region);
        HSSFRow headrow = sheet.createRow(1);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = headrow.createCell(i);
            cell.setCellStyle(headStyle);
            cell.setCellValue(title[i]);
        }

        //创建内容
        for(int i=0;i<contentList.size();i++){
            headrow = sheet.createRow(i + 2);
            for(int j=0;j<contentList.get(i).size();j++){
                // 调整宽度（15在这里表示一个像素）
                sheet.setColumnWidth(j, 256 * 15);
                //将内容按顺序赋给对应的列对象
                headrow.createCell(j).setCellValue(contentList.get(i).get(j));
                headrow.getCell(j).setCellStyle(cellStyle);

            }
        }
        return wb;
    }

    /**
     * 表头样式设置
     * @param workbook
     * @param sheet
     * @return
     */
    public static CellStyle headStyleTitle(HSSFWorkbook workbook, Sheet sheet) {
        //设置表格列宽度为15个字节
        sheet.setDefaultColumnWidth(15);

        HSSFFont font = workbook.createFont();

        // 字体大小
        font.setFontHeightInPoints((short)14);

        // 加粗
        font.setBold(true);

        CellStyle style = workbook.createCellStyle();

        // 自动换行
        style.setWrapText(true);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        //单元格背景颜色
//        style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
//        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(font);
        return style;
    }


    /**
     * 表头样式设置
     * @param workbook
     * @param sheet
     * @return
     */
    @SuppressWarnings("deprecation")
    public static CellStyle headStyle(HSSFWorkbook workbook, Sheet sheet) {
        //设置表格列宽度为15个字节
        sheet.setDefaultColumnWidth(15);

        HSSFFont font = workbook.createFont();

        // 字体大小
        font.setFontHeightInPoints((short)12);

        // 加粗
        font.setBold(true);

        CellStyle style = workbook.createCellStyle();

        // 自动换行
        style.setWrapText(true);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        //单元格背景颜色
//        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(font);
        return style;
    }

    public static CellStyle titleStyle(HSSFWorkbook workbook, Sheet sheet) {
        //设置表格列宽度为15个字节
        sheet.setDefaultColumnWidth(15);

        HSSFFont font = workbook.createFont();

        // 字体大小
        font.setFontHeightInPoints((short)12);

        // 加粗
        font.setBold(true);

        CellStyle style = workbook.createCellStyle();

        // 自动换行
        style.setWrapText(true);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        //单元格背景颜色
//        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(font);
        return style;
    }

    /**
     * 内容单元格样式设置
     * @param workbook
     * @param sheet
     * @return
     */
    @SuppressWarnings("deprecation")
    public static CellStyle cellStyle(HSSFWorkbook workbook, Sheet sheet) {
        //设置表格列宽度为15个字节
        sheet.setDefaultColumnWidth(15);

        HSSFFont font = workbook.createFont();

        // 字体大小
        font.setFontHeightInPoints((short)10);

        CellStyle style = workbook.createCellStyle();

        // 自动换行
        style.setWrapText(true);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        //单元格背景颜色
//        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(font);
        return style;
    }

    /**
     * 输出参数设置
     * @param response
     * @param fileName
     * @param workbook
     * @throws UnsupportedEncodingException
     * @throws IOException
     */
    public static void responseParam(HttpServletResponse response, String fileName, HSSFWorkbook workbook) throws IOException {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setCharacterEncoding("utf-8");
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        workbook.write(response.getOutputStream());
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 导出通用
     * @Author: wangz
     * @param request   请求
     * @param response  相应
     * @param fileName  文件名
     * @param header    第一行标题头
     * @param tableKey  对应列名
     * @param contentList 待导出数据
     */
    public static HSSFWorkbook exportExcelCommon(HttpServletRequest request,
                                                 HttpServletResponse response,
                                                 String fileName,
                                                 String[] header,
                                                 String[] tableKey,
                                                 List<Map<String, Object>> contentList){
        //2.1创建空的excel文档
        HSSFWorkbook wb = new HSSFWorkbook();
        //2.2创建空sheet页
        HSSFSheet sheet = wb.createSheet();
        //2.3创建标题行
        HSSFRow row = sheet.createRow(0);

        //创建标题
        for (int i = 0; i < header.length; i++) {
            row.createCell(i).setCellValue(header[i]);
        }

        //赋值
        for (int i = 0; i < contentList.size(); i++) {
            Map<String, Object> tableMap = contentList.get(i);
            Row indexRow = sheet.createRow(i + 1);
            //循环创建单元格
            for (int j = 0; j < tableKey.length; j++) {
                indexRow.createCell(j).setCellValue(getStr(tableMap.get(tableKey[j])));
            }
        }
        return  wb;
    }

    /**
     * 根据传入对象判断，若传入对象为空，则返回空串，否则返回其对应的字符串
     *
     * @param obj
     * @return
     */
    public static String getStr(Object obj) {
        return isEmpty(obj) ? "" : obj.toString();
    }

    /**
     * 校验为空或者为null
     *
     * @param pObj
     * @return
     */
    public static boolean isEmpty(Object pObj) {
        if (pObj == null) {
            return true;
        }
        if (pObj == "") {
            return true;
        }
        if (pObj instanceof String) {
            return ((String) pObj).length() == 0;
        } else if (pObj instanceof Collection) {
            return ((Collection) pObj).size() == 0;
        } else if (pObj instanceof Map) {
            return ((Map) pObj).size() == 0;
        }
        return false;
    }

    public static void responseParam(HttpServletRequest request, HttpServletResponse response, String fileName, HSSFWorkbook workbook) throws UnsupportedEncodingException, IOException {

        String agent = request.getHeader("User-Agent");
        //一个流两个头
        fileName = encodeDownloadFilename(fileName, agent);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setCharacterEncoding("utf-8");
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        workbook.write(response.getOutputStream());
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 浏览器兼容
     * @Author: limeijing
     * @return
     * @throws IOException
     */
    public static String encodeDownloadFilename(String filename, String agent)
            throws IOException {
        // 火狐浏览器
        if (agent.contains("Firefox")) {
            filename = "=?UTF-8?B?"
                    + new Base64().encode(filename.getBytes("utf-8"))
                    + "?=";
            filename = filename.replaceAll("\r\n", "");
        } else {
            // IE及其他浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+"," ");
        }
        return filename;
    }

    public static String check(MultipartFile file, String suffix){
        String message = "";
        if (file.isEmpty()) {
            message= "请选择需要上传的文件！";
        }
        //判断上传文件大小
        long size = file.getSize() / 1024;
        logger.info("上传文件：{} K", size);
        if (size > 10240) {
            message = "文件大小不可超过"+size/1024+"M，请分批上传。";
        }
        if(!"xlsx".equals(suffix) && !"xls".equals(suffix)){
            message = "上传格式不正确，请上传.xlsx或者.xls格式文件!";
        }
        return message;
    }

    public static String getCellValue(Cell cell) {
        if (null==cell || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = null;
        //判断cell类型
        switch(cell.getCellType()){
            case STRING:{
                //cellValue = cell.getRichStringCellValue().getString();
                cellValue = cell.getStringCellValue();
                break;
            }
            case NUMERIC:{
                //判断cell是否为日期格式
                if(DateUtil.isCellDateFormatted(cell)){
                    Date date = cell.getDateCellValue();
                    //转换为日期格式YYYY-mm-dd
                    cellValue = df.format(date);
                }else{
                    //数字
                    double value = cell.getNumericCellValue();
                    if (isENum(String.valueOf(value))) {
                        cellValue = ds.format(value).trim();
                    } else {
                        //yyyy-MM-dd----- 14
                        //yyyy年m月d日--- 31
                        //yyyy年m月------- 57
                        //m月d日  ---------- 58
                        //HH:mm----------- 20
                        //h时mm分  ------- 32
                        short format = cell.getCellStyle().getDataFormat();
                        if (format == 20 || format == 32 || format == 14 || format == 31 || format == 57 || format == 58) {
                            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                            Date date = DateUtil.getJavaDate(value);
                            cellValue = df.format(date);
                        }else{
                            cellValue = value+"";
                        }
                    }
                }
                break;
            }
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }


}
